﻿using AdvancedDataGridView;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace OracleCompare
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //textBox1.Text = "183.2.233.236";
            //textBox2.Text = "ELBTEST";
            //textBox3.Text = "ELBTEST";
            //textBox4.Text = "183.2.233.246";
            //textBox5.Text = "ELBONLINE";
            //textBox6.Text = "Bsy196OnLine";
            //textBox7.Text = "CINEMA_MOVIE";
            //textBox7.Text = "App_Info";
            //textBox7.Text = "CINEMA_ROOM";
            //textBox7.Text = "CINEMA_SEAT";
            //textBox7.Text = "User_Order_Produce";
            //textBox7.Text = "SYSRULE_SETDETAIL";
        }

        private bool isRun = false;

        private void button1_Click(object sender, EventArgs e)
        {
            if (isRun) return;
            isRun = true;
            button1.Enabled = false;
            Configs.SourceConnection = ConnectionString(textBox1.Text.Trim(), textBox2.Text, textBox3.Text);
            Configs.TargetConnection = ConnectionString(textBox4.Text.Trim(), textBox5.Text, textBox6.Text);
            if (string.IsNullOrWhiteSpace(Configs.SourceConnection) || string.IsNullOrWhiteSpace(Configs.TargetConnection))
            {
                MessageBox.Show("请填写数据库资料", "错误");
                isRun = false;
                button1.Enabled = true;
                return;
            }
            Configs.SourceTable = GetTable(Configs.SourceConnection, textBox7.Text);
            Configs.TargetTable = GetTable(Configs.TargetConnection, textBox7.Text);
            Configs.SameTable = new List<string>();
            IEnumerable<string> tables = Configs.SourceTable.Select(m => m.TableName).Distinct();
            treeGridView.Nodes.Clear();
            foreach (var t in tables)
            {
                var sourceCol = Configs.SourceTable.Where(m => m.TableName.Equals(t)).OrderBy(m => m.Index);
                var targetCol = Configs.TargetTable.Where(m => m.TableName.Equals(t)).OrderBy(m => m.Index);
                bool isSame = true;
                foreach (var c in sourceCol)
                {
                    var tcc = targetCol.FirstOrDefault(m => m.ColumnName.Equals(c.ColumnName));
                    if (null == tcc || !Configs.IsSameTableCol(c, tcc))
                    {
                        isSame = false;
                        break;
                    }
                }

                string title = "相同";
                string clickType = "双击查看SQL脚本";
                if (!targetCol.Any()) { title = "新的"; }
                else if (!isSame) { title = "定义不同"; }
                else { Configs.SameTable.Add(t); clickType = "双击数据对比"; }
                TreeGridNode node = treeGridView.Nodes.Add(title, t, clickType);

                int i = 0;
                foreach (var c in sourceCol)
                {
                    var tc = targetCol.FirstOrDefault(m => m.ColumnName.Equals(c.ColumnName));
                    title = "相同";
                    if (null == tc) { title = "新的"; tc = new TableCol(); }
                    else if (!Configs.IsSameTableCol(c, tc)) { title = "定义不同"; }
                    if (i < 1)
                    {
                        node = node.Nodes.Add(title, c.ColumnName);
                    }
                    else
                    {
                        node = node.Parent.Nodes.Add(title, c.ColumnName);
                    }
                    TreeGridNode node2 = node.Nodes.Add("数据类型", c.DataType, tc.DataType);
                    node2 = node2.Parent.Nodes.Add("数据长度", c.DataLength, tc.DataLength);
                    node2 = node2.Parent.Nodes.Add("精度", c.DataPrecision, tc.DataPrecision);
                    node2 = node2.Parent.Nodes.Add("比例", c.DataScale, tc.DataScale);
                    node2 = node2.Parent.Nodes.Add("主键", c.IsKey, tc.IsKey);
                    node2 = node2.Parent.Nodes.Add("允许空", c.Nullable, tc.Nullable);
                    node2 = node2.Parent.Nodes.Add("唯一键", c.Unique, tc.Unique);
                    node2 = node2.Parent.Nodes.Add("默认值", c.DataDefault, tc.DataDefault);
                    node2 = node2.Parent.Nodes.Add("备注", c.Columndes, tc.Columndes);
                    node2 = node2.Parent.Nodes.Add("外键", null == c.ForeignKey ? "无" : (c.ForeignKey.TableName + "." + c.ForeignKey.ColumnName + ":" + c.ForeignKey.DataDefault + "-" + c.ForeignKey.Nullable), null == tc.ForeignKey ? "无" : (tc.ForeignKey.TableName + "." + tc.ForeignKey.ColumnName + ":" + tc.ForeignKey.DataDefault + "-" + tc.ForeignKey.Nullable));
                    node2 = node2.Parent.Nodes.Add("排序", c.Index, tc.Index);
                    i++;
                }
            }
            isRun = false;
            button1.Enabled = true;
        }

        private void treeGridView_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (null == treeGridView.Rows[e.RowIndex].Cells[2].Value) return;
            string tableName = treeGridView.Rows[e.RowIndex].Cells[1].Value.ToString();
            switch (treeGridView.Rows[e.RowIndex].Cells[2].Value.ToString())
            {
                //case "双击数据对比":
                case "双击查看SQL脚本":
                    MessageText mt = new MessageText();
                    StringBuilder sb = new StringBuilder();
                    sb.Append(string.Format("CREATE TABLE \"{0}\" (\r\n", tableName));
                    var list = Configs.SourceTable.Where(m => m.TableName.Equals(tableName)).OrderBy(m => m.Index);
                    List<string> cSql = new List<string>();
                    foreach (var l in list)
                    {
                        string oth = "";
                        if (!string.IsNullOrEmpty(l.DataDefault)) { oth += " DEFAULT " + l.DataDefault; }
                        if (!l.Nullable) { oth += " NOT NULL"; }
                        if (l.DataType.Contains("CHAR"))
                        {
                            cSql.Add(string.Format("\"{0}\" {1}({2}){3}", l.ColumnName, l.DataType, l.DataType.StartsWith("N") ? (l.DataLength / 2) : l.DataLength, oth));
                        }
                        else if (l.DataType.Contains("DATE") || l.DataType.StartsWith("LONG") || l.DataType.StartsWith("RAW") || l.DataType.StartsWith("INT"))
                        {
                            cSql.Add(string.Format("\"{0}\" {1}{2}", l.ColumnName, l.DataType, oth));
                        }
                        else if (l.DataType.Equals("NUMBER") || l.DataType.Contains("FLOAT") || l.DataType.Contains("DOUBLE"))
                        {
                            if (l.DataPrecision.HasValue && l.DataPrecision != 0)
                            {
                                if (l.DataScale.HasValue && l.DataScale != 0)
                                {
                                    cSql.Add(string.Format("\"{0}\" {1}({2},{3}){4}", l.ColumnName, l.DataType, l.DataPrecision, l.DataScale, oth));
                                }
                                else
                                {
                                    cSql.Add(string.Format("\"{0}\" {1}({2}){3}", l.ColumnName, l.DataType, l.DataPrecision, oth));
                                }
                            }
                            else
                            {
                                cSql.Add(string.Format("\"{0}\" {1}{2}", l.ColumnName, l.DataType, oth));
                            }
                        }
                        else
                        {
                            switch (l.DataType)
                            {
                                default:
                                    cSql.Add(string.Format("-- \"{0}\" {1}{2}", l.ColumnName, l.DataType, oth));
                                    break;
                            }
                        }
                    }
                    sb.Append(string.Join(",\r\n", cSql.ToArray()));
                    sb.Append(");");
                    //备注
                    string tableComments = GetTableComments(Configs.SourceConnection, tableName);
                    if (!string.IsNullOrEmpty(tableComments)) { sb.Append(string.Format("\r\nCOMMENT ON TABLE \"{0}\" IS '{1}';", tableName, tableComments.Replace("'", "''"))); }
                    foreach (var l in list.Where(m => !string.IsNullOrEmpty(m.Columndes)))
                    {
                        sb.Append(string.Format("\r\nCOMMENT ON COLUMN \"{0}\".\"{1}\" IS '{2}';", tableName, l.ColumnName, l.Columndes.Replace("'", "''")));
                    }
                    //唯一键
                    var uniques = list.Where(m => m.Unique.HasValue);
                    if (uniques.Any())
                    {
                        cSql.Clear();
                        foreach (var u in uniques.Where(m => m.Unique.Value == true).Select(m => m.ColumnName).Distinct())
                        {
                            cSql.Add(string.Format("\"{0}\"", u));
                        }
                        if (cSql.Any())
                            sb.Append(string.Format("\r\nALTER TABLE \"{0}\" ADD UNIQUE ({1});", tableName, string.Join(",", cSql.ToArray())));
                        cSql.Clear();
                        foreach (var u in uniques.Where(m => m.Unique.Value == false).Select(m => m.ColumnName).Distinct())
                        {
                            cSql.Add(string.Format("\"{0}\"", u));
                        }
                        if (cSql.Any())
                            sb.Append(string.Format("\r\nALTER TABLE \"{0}\" ADD UNIQUE ({1}) DISABLE;", tableName, string.Join(",", cSql.ToArray())));
                    }
                    //检查
                    foreach (var l in list.Where(m => !string.IsNullOrEmpty(m.Check)))
                    {
                        var check = l.Check.Split(';');
                        foreach (var c in check)
                        {
                            sb.Append(string.Format("\r\nALTER TABLE \"{0}\" ADD CHECK ({1};", tableName, c));
                        }
                    }
                    //主键
                    var keys = list.Where(m => m.IsKey);
                    if (keys.Any())
                    {
                        cSql.Clear();
                        foreach (var k in keys.Select(m => m.ColumnName).Distinct())
                        {
                            cSql.Add(string.Format("\"{0}\"", k));
                        }
                        sb.Append(string.Format("\r\nALTER TABLE \"{0}\" ADD PRIMARY KEY ({1});", tableName, string.Join(",", cSql.ToArray())));
                    }
                    //外键
                    var foreignKeys = list.Where(m => m.ForeignKey != null);
                    foreach (var t in foreignKeys.Select(m => m.ForeignKey.TableName).Distinct())
                    {
                        var fList = foreignKeys.Where(m => m.ForeignKey.TableName.Equals(t));
                        string del = (string.IsNullOrWhiteSpace(fList.First().ForeignKey.DataDefault) || "NO ACTION".Equals(fList.First().ForeignKey.DataDefault)) ? "" : (" ON DELETE " + fList.First().ForeignKey.DataDefault);
                        string status = fList.First().ForeignKey.Nullable ? "" : " DISABLE";
                        cSql.Clear();
                        List<string> fSql = new List<string>();
                        foreach (var f in fList)
                        {
                            cSql.Add(string.Format("\"{0}\"", f.ColumnName));
                            fSql.Add(string.Format("\"{0}\"", f.ForeignKey.ColumnName));
                        }
                        sb.Append(string.Format("\r\nALTER TABLE \"{0}\" ADD FOREIGN KEY ({1}) REFERENCES \"{2}\" ({3}){4}{5};", tableName, string.Join(",", cSql.ToArray()), t, string.Join(",", fSql.ToArray()), del, status));
                    }
                    mt.msg = sb.ToString();
                    mt.ShowDialog();
                    break;
                case "双击数据对比":
                    if (!Configs.SameTable.Contains(tableName)) { return; }
                    Form2 form = new Form2();
                    form.tableName = tableName;
                    form.ShowDialog();
                    break;
            }
        }

        private string ConnectionString(string ip, string user, string pass)
        {
            if (string.IsNullOrEmpty(ip) || string.IsNullOrEmpty(user) || string.IsNullOrEmpty(pass)) { return ""; }
            return string.Format("data source={0}/ORCL;user id={1};password={2};", ip, user, pass);
        }

        private List<TableCol> GetTable(string connectionString, string keywords = "")
        {
            string[] connection = connectionString.Split(';');
            connection = connection[1].Split('=');
            string user = connection[1];
            List<TableCol> list = new List<TableCol>();
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.CommandText = @"CREATE OR REPLACE 
FUNCTION GetColumnDefaultValue(
    TableName IN varchar2,
    ColumnName IN varchar2
  )
  RETURN varchar2
    AS ddLong long; 
    BEGIN
      SELECT data_default
      INTO ddLong
      FROM user_tab_columns
      WHERE table_name = TableName
        AND column_name = ColumnName;
    RETURN
      substr(ddLong,0,255);
    END;";
                    cmd.ExecuteNonQuery();
                    cmd = conn.CreateCommand();
                    if (!string.IsNullOrEmpty(keywords)) { keywords = string.Format(" AND TABLE_NAME LIKE '%{0}%'", keywords); }
                    cmd.CommandText = string.Format("SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '{0}'{1}", user, keywords);
                    OracleDataReader odr = cmd.ExecuteReader();
                    while (odr.Read())
                    {
                        string tableName = odr.GetOracleString(0).ToString();
                        //查询主键&唯一键&外键&检查
                        cmd = conn.CreateCommand();
                        cmd.CommandText = string.Format("SELECT COL.COLUMN_NAME,CON.CONSTRAINT_TYPE,CON.STATUS,CON.R_CONSTRAINT_NAME,CON.DELETE_RULE,CON.SEARCH_CONDITION FROM ALL_CONSTRAINTS CON INNER JOIN ALL_CONS_COLUMNS COL ON CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME WHERE CON. OWNER = '{0}' AND COL.OWNER='{0}' AND COL.TABLE_NAME = '{1}'", user, tableName);
                        List<string> keys = new List<string>();
                        List<Dictionary<string, string>> uniques = new List<Dictionary<string, string>>();
                        List<Dictionary<string, string>> foreignKeys = new List<Dictionary<string, string>>();
                        List<Dictionary<string, string>> checks = new List<Dictionary<string, string>>();
                        OracleDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            if (null != dr[1])
                            {
                                switch (dr[1].ToString())
                                {
                                    case "P":
                                        //主键
                                        keys.Add(dr.GetOracleString(0).ToString());
                                        break;
                                    case "U":
                                        //唯一键
                                        var u = new Dictionary<string, string>();
                                        u.Add("name", dr[0].ToString());
                                        u.Add("status", dr[2].ToString());
                                        uniques.Add(u);
                                        break;
                                    case "R":
                                        //外键
                                        if (null != dr[3] && !string.IsNullOrWhiteSpace(dr[3].ToString()))
                                        {
                                            var f = new Dictionary<string, string>();
                                            f.Add("name", dr[0].ToString());
                                            f.Add("status", dr[2].ToString());
                                            f.Add("foreignId", dr[3].ToString());
                                            f.Add("delRule", dr[4].ToString());
                                            foreignKeys.Add(f);
                                        }
                                        break;
                                    case "C":
                                        //检查
                                        if (null != dr[5] && !string.IsNullOrWhiteSpace(dr[5].ToString()))
                                        {
                                            var c = new Dictionary<string, string>();
                                            c.Add("name", dr[0].ToString());
                                            c.Add("status", dr[2].ToString());
                                            c.Add("check", dr[5].ToString());
                                            checks.Add(c);
                                        }
                                        break;
                                }
                            }
                        }
                        dr.Close();
                        //查询字段
                        cmd = conn.CreateCommand();
                        cmd.CommandText = string.Format("SELECT TA.COLUMN_NAME,TA.DATA_TYPE,TA.DATA_LENGTH,TA.DATA_PRECISION,TA.DATA_SCALE,TA.NULLABLE,TA.DATA_DEFAULT,TB.COMMENTS,TA.COLUMN_ID FROM ALL_TAB_COLUMNS TA INNER JOIN ALL_COL_COMMENTS TB ON TA.TABLE_NAME = TB.TABLE_NAME AND TA.COLUMN_NAME = TB.COLUMN_NAME WHERE TA.OWNER = '{0}' AND TB.OWNER = '{0}' AND TA.TABLE_NAME = '{1}' ORDER BY TA.COLUMN_ID", user, tableName);
                        dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            var entity = new TableCol()
                            {
                                TableName = tableName,
                                ColumnName = dr[0].ToString(),
                                DataType = dr[1].ToString(),
                                DataLength = 0,
                                IsKey = keys.Contains(dr[0].ToString()),
                                Nullable = "Y".Equals(dr[5]),
                                DataDefault = null == dr[6] ? "" : dr[6].ToString(),
                                Columndes = null == dr[7] ? "" : dr[7].ToString(),
                                Index = 0
                            };
                            if (null != dr[2])
                            {
                                uint i;
                                if (uint.TryParse(dr[2].ToString(), out i))
                                {
                                    entity.DataLength = i;
                                }
                            }
                            if (null != dr[3])
                            {
                                uint i;
                                if (uint.TryParse(dr[3].ToString(), out i))
                                {
                                    entity.DataPrecision = i;
                                }
                            }
                            if (null != dr[4])
                            {
                                int i;
                                if (int.TryParse(dr[4].ToString(), out i))
                                {
                                    entity.DataScale = i;
                                }
                            }
                            if (null != dr[8])
                            {
                                uint i;
                                if (uint.TryParse(dr[8].ToString(), out i))
                                {
                                    entity.Index = i;
                                }
                            }
                            //默认值
                            OracleCommand oc = conn.CreateCommand();
                            oc.CommandText = string.Format("SELECT GetColumnDefaultValue('{0}','{1}') FROM dual", entity.TableName, entity.ColumnName);
                            OracleDataReader ocodr = oc.ExecuteReader();
                            while (ocodr.Read())
                            {
                                entity.DataDefault = null == ocodr[0] ? "" : ocodr[0].ToString().Trim();
                            }
                            ocodr.Close();
                            //唯一键
                            var u = uniques.FirstOrDefault(m => m["name"].Equals(entity.ColumnName));
                            if (null != u)
                            {
                                switch (u["status"])
                                {
                                    case "ENABLED":
                                        entity.Unique = true;
                                        break;
                                    case "DISABLED":
                                        entity.Unique = false;
                                        break;
                                }
                            }
                            //外键
                            var f = foreignKeys.FirstOrDefault(m => m["name"].Equals(entity.ColumnName));
                            if (null != f)
                            {
                                oc = conn.CreateCommand();
                                oc.CommandText = string.Format("SELECT TABLE_NAME,COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE OWNER = '{0}' AND CONSTRAINT_NAME = '{1}'", user, f["foreignId"]);
                                ocodr = oc.ExecuteReader();
                                while (ocodr.Read())
                                {
                                    entity.ForeignKey = new TableCol()
                                    {
                                        TableName = ocodr[0].ToString(),
                                        ColumnName = ocodr[1].ToString(),
                                        DataDefault = f["delRule"],
                                        Nullable = f["status"].Equals("ENABLED")
                                    };
                                }
                                ocodr.Close();
                            }
                            //检查
                            entity.Check = "";
                            foreach (var c in checks.Where(m => m["name"].Equals(entity.ColumnName)))
                            {
                                entity.Check += c["check"] + ")" + ("DISABLE".Equals(c["status"]) ? c["status"] : "") + ";";
                            }
                            list.Add(entity);
                        }
                        dr.Close();
                    }
                    odr.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("读取数据库失败：" + ex.Message, "错误");
                }
                finally
                {
                    conn.Close();
                }
            }
            return list;
        }

        private string GetTableComments(string connectionString, string tableName)
        {
            string s = "";
            string[] connection = connectionString.Split(';');
            connection = connection[1].Split('=');
            string user = connection[1];
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.CommandText = string.Format("SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER = '{0}' AND TABLE_TYPE = 'TABLE' AND COMMENTS IS NOT NULL AND TABLE_NAME = '{1}'", user, tableName);
                    OracleDataReader odr = cmd.ExecuteReader();
                    while (odr.Read())
                    {
                        s = null == odr[0] ? "" : odr[0].ToString();
                    }
                    odr.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("读取表" + tableName + "备注失败：" + ex.Message, "错误");
                }
                finally
                {
                    conn.Close();
                }
            }
            return s;
        }
    }
}